package codemaster.persistence;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import codemaster.core.Constant;
import codemaster.core.Helper;
import codemaster.dao.DaoImplBuilder;

import com.Ostermiller.util.ExcelCSVParser;

import common.util.FileUtility;
import common.util.StringUtility;

public class SqlMapBuilder {
	
	public String getPK(String[][] table)
	{
		String pk = null;
		for (int i = 3; i < table.length; i++) 
		{
			if (table[i][Constant.C_PRIMARY_KEY].trim()
					.equalsIgnoreCase(Constant.YES))
					{
						if (pk == null)
						pk = table[i][Constant.C_JAVA_TYPE];
						else
							pk = "java.util.Map";
					}
		}
		return pk;
	}
	
	class IndexInfo
	{
		String indexName;
		List<String> columnNames = new ArrayList<String>();
		List<String> columnTypes = new ArrayList<String>();
		boolean isUnique;
		
		String getIndexString()
		{
			String columnNamesString = null;
			for(int i =0; i < columnNames.size(); i++)
			{
				if(columnNamesString == null)
				{
					columnNamesString = columnNames.get(i);
				}
				else
				{
					columnNamesString = columnNamesString + "," + columnNames.get(i);
				}
			}
			
			return columnNamesString;
		}
		
		String getIndexString2()
		{
			String columnNamesString = null;
			for(int i =0; i < columnNames.size(); i++)
			{
				if(columnNamesString == null)
				{
					columnNamesString = columnTypes.get(i) + " " + columnNames.get(i);
				}
				else
				{
					columnNamesString = columnNamesString + "," + columnTypes.get(i) + " " + columnNames.get(i);
				}
			}
			
			return columnNamesString;
		}
		
		String getParameterString()
		{
			if (columnNames.size() > 1)
			{
				return "java.util.Map";
			}
			else
			{
				return columnTypes.get(0);
			}
		}
		
	}
	
	public Map<String, IndexInfo> getIndex(String[][] table)
	{
		Map<String, IndexInfo> indexInfos = new HashMap<String, IndexInfo>();
		for (int i = 3; i < table.length; i++) 
		{
			String indexName = table[i][Constant.C_INDEX].trim();
			if (indexName.isEmpty() == false)
			{
				IndexInfo indexInfo = indexInfos.get(indexName);
				
				if (indexInfo == null)
				{
					indexInfo = new IndexInfo();
				    indexInfos.put(indexName, indexInfo);
				}
				indexInfo.indexName = table[i][Constant.C_INDEX].trim();
				indexInfo.isUnique = table[i][Constant.C_INDEX_ISUNIQUE].trim().isEmpty() ? false : true;
				indexInfo.columnNames.add(table[i][Constant.C_NAME]);
				indexInfo.columnTypes.add(table[i][Constant.C_JAVA_TYPE]);
			}
		}
		return indexInfos;
	}
	
	
	/**
	 * Builds a sql script file
	 * 
	 * @param inputFile
	 *            the input file
	 * @param outputDir
	 *            the output directory
	 * @param dbType
	 *            the database type
	 */
	public void build(File inputFile, File outputDir, String dbType,
			String xmlPackage,String beanPackage) {
		// Determines the table name from the input file name
		String entityName = FileUtility.getFileNameWithoutExtension(inputFile);
		String parameterClass = entityName;
//		String parameterClass = entityName.substring(0, 1).toLowerCase()
//				+ entityName.substring(1);
		String tableName ;//getTableName(entityName);
		BufferedWriter bfw = null;
		BufferedReader bfr = null;
		
		dbType="";
//		dbType += ("."+entityName);
		try {
			// Reads the table columns
			bfr = new BufferedReader(new FileReader(inputFile));
			String[][] table = ExcelCSVParser.parse(bfr);
			tableName = table[0][0];
			bfr.close();
			// Creates the sql script file at the destination folder
			String dbDir = Helper.getPackageDir(outputDir, xmlPackage);
			boolean isFirst = true;
			(new File(dbDir)).mkdirs();
			bfw = new BufferedWriter(new FileWriter(dbDir + File.separator
					+ entityName + ".xml"));

			// writes header
			bfw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n");
			bfw.write("\n");
			bfw
					.write("<!DOCTYPE sqlMap PUBLIC \"-//ibatis.apache.org//DTD SQL Map 2.0//EN\"\n");
			
			bfw.write("\t \"http://ibatis.apache.org/dtd/sql-map-2.dtd\">\n");
			bfw.write("\n");

			// writes namespace
			bfw.write("<sqlMap namespace=\"" + entityName + "\">\n");
			bfw.write("\t<typeAlias alias=\"" + entityName
					+ "\" type=\"" + beanPackage + "." + entityName
					+ "\"/>\n\n");
			// writes insert
			bfw.write("\t<insert id=\"insert" + entityName
					+ "\" parameterClass=\"" + parameterClass + "\">\n");
			bfw.write("\t\tINSERT INTO " + tableName + "(\n");
			// Writes table columns
			bfw.write("\t\t\t");
			int columnCounter = 0;
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES)) {
					// If current attribute is currentEntity UID, skip it
					if (isUid(table[i][Constant.C_NAME])) {
						continue;
					}
					if (i == table.length - 1) {
						bfw.write(getColumnName(table[i][Constant.C_NAME])
								+ "\n");
					} else {
						bfw.write(getColumnName(table[i][Constant.C_NAME])
								+ ", ");
						columnCounter++;
						if (columnCounter % 5 == 0) {
							bfw.write("\n\t\t\t");
						}
					}
				}
			}
			bfw.write("\t\t)VALUES(\n");
			columnCounter = 0;
			bfw.write("\t\t\t");
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][Constant.C_NON_PERSISTENT].trim()
						.equalsIgnoreCase(Constant.YES)) {
					// If current attribute is currentEntity UID, skip it
					if (isUid(table[i][Constant.C_NAME])) {
						continue;
					}
					if (i == table.length - 1) {
						bfw.write("#" + table[i][Constant.C_NAME] + "#" + "\n");
					} else {
						bfw.write("#" + table[i][Constant.C_NAME] + "#, ");
						columnCounter++;
						if (columnCounter % 5 == 0) {
							bfw.write("\n\t\t\t");
						}
					}
				}
			}
			bfw.write("\t\t)\n");
			bfw.write("\t</insert>\n\n");
			
			
			// Writes the select<Table> mapping
			bfw.write("\t<select id=\"select" + entityName
					+ "\" resultClass=\"" + parameterClass
					+ "\" parameterClass=\""+getPK(table)+"\">\n");
			bfw.write("\t\tSELECT\n");
			String selectColumnList = getColumnList(table);
			bfw.write(selectColumnList);
			// TODO: add logic to list all tables involved here
			bfw.write("\t\tFROM " + tableName + " \n");
			bfw.write("\t\tWHERE ");
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES)) {
					// If current attribute is primary key, writes it
					if (table[i][Constant.C_PRIMARY_KEY].equals(Constant.YES)) {
						if (isFirst) {
							bfw.write(getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
							//break;
						} else {
							bfw.write("\t\t\tAND "
									+ getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
						}
						isFirst = false;
					}
				}
			}
			bfw.write("\t</select>\n\n");
			
			
			
			Map<String, IndexInfo> indexInfos = getIndex(table);
			for(IndexInfo indexInfo : indexInfos.values())
			{
				if (indexInfo.isUnique)
				{
				bfw.write("\t<select id=\"select" + entityName + "By" +indexInfo.indexName
						+ "\" resultClass=\"" + parameterClass
						+ "\" parameterClass=\""+indexInfo.getParameterString()+"\">\n");
				}
				else
				{
					bfw.write("\t<select id=\"select" + entityName +"List" + "By" +indexInfo.indexName
							+ "\" resultClass=\"" + parameterClass
							+ "\" parameterClass=\""+indexInfo.getParameterString()+"\">\n");
				}
				
				bfw.write("\t\tSELECT\n");
				bfw.write(selectColumnList);
				// TODO: add logic to list all tables involved here
				bfw.write("\t\tFROM " + tableName + " \n");
				bfw.write("\t\tWHERE ");
				isFirst = true;
				for (String columnName : indexInfo.columnNames) {
							if (isFirst) {
								bfw.write(columnName
										+ " = #" + columnName
										+ "#\n");
								//break;
							} else {
								bfw.write("\t\t\tAND "
										+ columnName
										+ " = #" + columnName
										+ "#\n");
							}
							isFirst = false;
				}
				bfw.write("\t</select>\n\n");
			}
			
			
			
	
			bfw.write("\t<select id=\"select" + entityName + "List"
					+ "\" resultClass=\"" + parameterClass + "\" >\n");
			bfw.write("\t\tSELECT\n");
			bfw.write(selectColumnList);
			// TODO: add logic to list all tables involved here
			bfw.write("\t\tFROM " + tableName + " \n");
			bfw.write("\t</select>\n\n");
			//Writes select Total
//			bfw.write("\t<select id=\"select" + entityName + "Total"
//					+ "\" resultClass=\"int\" parameterClass=\"map\">\n");
//			bfw.write("\t\tSELECT count(1) as value \n");
//			bfw.write("\t\tFROM " + tableName + " \n");
//			bfw.write("\t\tWHERE 1 = 1 \n");			
//			for (int i = 3; i < table.length; i++) {
//				// int type
//				if ("int".equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())
//						|| "long"
//								.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())
//						|| "boolean"
//								.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())
//						|| "Integer"
//								.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())) {
//					bfw.write("\t\t\t<isNotEmpty prepend=\"AND\" property=\""
//							+ table[i][Constant.C_NAME] + "\">\n");
//					bfw.write("\t\t\t"
//							+ getColumnName(table[i][Constant.C_NAME]) + " = #{"
//							+ table[i][Constant.C_NAME] + "}\n");
//
//					bfw.write("\t\t\t</isNotEmpty>\n");
//				} else if ("string"
//						.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())) {
//					bfw.write("\t\t\t<isNotEmpty prepend=\"AND\" property=\""
//							+ table[i][Constant.C_NAME] + "\">\n");
//					bfw.write("\t\t\t"
//							+ getColumnName(table[i][Constant.C_NAME])
//							+ " like '%$" + table[i][Constant.C_NAME] + "$%'\n");
//					bfw.write("\t\t\t</isNotEmpty>\n");
//				} else if ("date"
//						.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())
//						|| "Timestamp"
//								.equalsIgnoreCase(table[i][Constant.C_JAVA_TYPE].trim())) {
//
//					bfw.write("\t\t\t<isNotEmpty prepend=\"AND\" property=\"s_"
//							+ table[i][Constant.C_NAME] + "\">\n");
//					bfw.write("\t\t\t"
//							+ getColumnName(table[i][Constant.C_NAME])
//							+ " >= #{" + table[i][Constant.C_NAME] + "}\n");
//					bfw.write("\t\t\t</isNotEmpty>\n");
//					
//					bfw.write("\t\t\t<isNotEmpty prepend=\"AND\" property=\"e_"
//							+ table[i][Constant.C_NAME] + "\">\n");
//					bfw.write("\t\t\t <![CDATA["
//							+ getColumnName(table[i][Constant.C_NAME])
//							+ " < #{" + table[i][Constant.C_NAME] + "}]]>\n");
//					bfw.write("\t\t\t</isNotEmpty>\n");
//				}
//
//			}
//			
//			
//			
//			bfw.write("\t</select>\n\n");
			
			
			
			
			
			
			
			
			//
			// Writes update
			//
			bfw.write("\t<update id=\"update" + entityName
					+ "\" parameterClass=\"" + parameterClass + "\">\n");
			bfw.write("\t\tUPDATE " + tableName + "\n\t\tSET\n");
			// Writes table columns
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES)) {
					// If current attribute is primary key, skip it
					if (table[i][Constant.C_PRIMARY_KEY].equals(Constant.YES)) {
						continue;
					}
					if (i == table.length - 1) {
						bfw.write("\t\t\t"
								+ getColumnName(table[i][Constant.C_NAME])
								+ " = #" + table[i][Constant.C_NAME] + "#\n");
					} else {
						bfw.write("\t\t\t"
								+ getColumnName(table[i][Constant.C_NAME])
								+ " = #" + table[i][Constant.C_NAME] + "#,\n");
					}
				}
			}
			bfw.write("\t\tWHERE ");
			// Writes table columns
			isFirst = true;
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES)) {
					// If current attribute is primary key, writes it
					if (table[i][Constant.C_PRIMARY_KEY].equals(Constant.YES)) {
						if (isFirst) {
							bfw.write(getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
						} else {
							bfw.write("\t\t\tAND "
									+ getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
						}
						isFirst = false;
					}
				}
			}
			bfw.write("\t</update>\n\n");
			
			//
			// Writes delete
			//
			bfw.write("\t<delete id=\"delete" + entityName
					+ "\" parameterClass=\"" + parameterClass + "\">\n");
			bfw.write("\t\tDELETE FROM " + tableName + "\n\t\t");
			bfw.write("\t\tWHERE ");
			// Writes table columns
			isFirst = true;
			for (int i = 3; i < table.length; i++) {
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES)) {
					// If current attribute is primary key, writes it
					if (table[i][Constant.C_PRIMARY_KEY].equals(Constant.YES)) {
						if (isFirst) {
							bfw.write(getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
						} else {
							bfw.write("\t\t\tAND "
									+ getColumnName(table[i][Constant.C_NAME])
									+ " = #" + table[i][Constant.C_NAME]
									+ "#\n");
						}
						isFirst = false;
					}
				}
			}
			bfw.write("\t</delete>\n");
			bfw.write("</sqlMap>\n");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (bfw != null) {
				try {
					bfw.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * @param bfw
	 * @param table
	 * @param hasJoin
	 * @return
	 * @throws IOException
	 */
	private String getColumnList(String[][] table) throws IOException {
		StringBuffer sb = new StringBuffer();
		for (int i = 3; i < table.length; i++) {
			sb.append("\t\t\t");
			// If non-persistent attribute is Y, writes the external table
			// name
			if (!StringUtility.isEmpty(table[i][Constant.C_TABLE])) {
				sb.append(table[i][Constant.C_TABLE] + ".");
			}
			if (getColumnName(table[i][Constant.C_NAME]).equals(
					table[i][Constant.C_NAME])) {
				sb.append(getColumnName(table[i][Constant.C_NAME]));
			} else {
				sb.append(getColumnName(table[i][Constant.C_NAME]) + " AS "
						+ table[i][Constant.C_NAME]);
			}
			if (i < table.length - 1) {
				sb.append(",\n");
			} else {
				sb.append("\n");
			}
		}
		return sb.toString();
	}

	/**
	 * Gets script to generate a column
	 * 
	 * @param name
	 *            attribute name
	 * @param dataType
	 *            database data type
	 * @param key
	 *            flag to indicate if current attribute is part of the primary
	 *            key
	 * @param nullable
	 *            flag to indicate if current attribute is nullable
	 * @param defaultValue
	 * @return script to generate a column
	 */
	private String getColumn(String name, String dataType, String nullable,
			String defaultValue, String autoIncrement) {
		StringBuffer sb = new StringBuffer();
		sb.append("\t" + name + " " + dataType);
		if (!nullable.equalsIgnoreCase(Constant.YES)) {
			sb.append(" NOT NULL");
		}
		if (autoIncrement.equalsIgnoreCase(Constant.YES)) {
			sb.append(" AUTO_INCREMENT");
		}
		sb.append(",\n");
		return sb.toString();
	}

	/**
	 * Get the column name from the attribute name
	 * 
	 * @param attribute
	 *            attribute name
	 * @return column name
	 */
	private String getColumnName(String attribute) {
		return attribute;
//		if(attribute.toLowerCase() .equals("pkid"))
//		{
//			return attribute;
//		}
//		StringBuffer sb = new StringBuffer();
//		char[] c = attribute.toCharArray();
//		for (int i = 0; i < c.length; i++) {
//			if (c[i] >= 'A' && c[i] <= 'Z') {
//				sb.append(("_" + c[i]).toLowerCase());
//			} else {
//				sb.append(c[i]);
//			}
//		}
//		return sb.toString();
	}

	/**
	 * Gets the primary key constraint
	 * 
	 * @param tableName
	 *            table name
	 * @param table
	 *            table attributes
	 * @return the script for the table primary key constraint
	 */
	private String getPrimaryKeyConstraint(String tableName, String[][] table) {
		StringBuffer sb = new StringBuffer();
		sb.append("\tCONSTRAINT pk_" + tableName + " PRIMARY KEY (");
		boolean isFirst = true;
		for (int i = 0; i < table.length; i++) {
			if ((table[i][4]).equalsIgnoreCase(Constant.YES)) {
				if (isFirst) {
					sb.append(getColumnName(table[i][Constant.C_NAME]));
				} else {
					sb.append(", " + getColumnName(table[i][Constant.C_NAME]));
				}
				isFirst = false;
			}
		}
		sb.append(")");
		return sb.toString();
	}

	/**
	 * Gets the unique constraint
	 * 
	 * @param tableName
	 *            table name
	 * @param table
	 *            table attributes
	 * @return the script for the table unique constraint
	 */
	private String getUniqueConstraint(String tableName, String[][] table) {
		StringBuffer sb = new StringBuffer();
		sb.append("\tCONSTRAINT uk_" + tableName + " UNIQUE (");
		boolean isFirst = true;
		boolean hasUnique = false;
		for (int i = 0; i < table.length; i++) {
			if ((table[i][Constant.C_UNIQUE_KEY])
					.equalsIgnoreCase(Constant.YES)
					&& !(table[i][Constant.C_NON_PERSISTENT])
							.equalsIgnoreCase(Constant.YES)) {
				hasUnique = true;
				if (isFirst) {
					sb.append(getColumnName(table[i][Constant.C_NAME]));
				} else {
					sb.append(", " + getColumnName(table[i][Constant.C_NAME]));
				}
				isFirst = false;
			}
		}
		sb.append(")");
		// If no unique key, returns null
		if (!hasUnique) {
			return null;
		}
		return sb.toString();
	}

	private String getTableName(String fileName) {
		StringBuffer sb = new StringBuffer();
		sb.append(fileName.substring(0, 1).toLowerCase());
		for (int i = 1; i < fileName.length(); i++) {
			if (fileName.charAt(i) >= 'A' && fileName.charAt(i) <= 'Z') {
				sb.append("_" + fileName.substring(i, i + 1).toLowerCase());
			} else {
				sb.append(fileName.charAt(i));
			}
		}
		return sb.toString();
	}

	private boolean isUid(String attribute) {
		if (attribute.toLowerCase().equals("pkid")) {
			return true;
		}
		return false;
	}
}
